Imports System.Data.Odbc
Imports System.Data.SqlClient

Public Class dbInterface
	' will need to change the connection string based on the final server info
	'Protected connStr As String = Globals.DBConnection
	'Protected connStr As String = "Server=GASVR03.gaoptions.local;uid=mirs;pwd=gamexico;database=GAOpts"	'"SERVER=(local);Trusted_connection=yes;DATABASE=GAOpts;"    '"workstation id=DURON-750;packet size=4096;integrated security=SSPI;data source=(local);persist security info=False;initial catalog=GAOpts"
	Protected m_conn As SqlConnection
	Protected m_comm As SqlCommand
	Protected m_dr As SqlDataReader
	Protected m_sp As SqlCommand

	Public Sub New()

		'		If Application.ExecutablePath = "C:\Documents and Settings\pallison\My Documents\Customers\GAGlobalMarkets\MIRS\MIRS_StreamServer\bin\MIRS_StreamServer.exe" Then
		'		connStr = "Server=(local);uid=mirs;pwd=gamexico;database=GAOpts"
		'		End If
	End Sub

	Public ReadOnly Property Connection() As SqlConnection
		Get
			If m_conn Is Nothing Then
				openConnection()
			End If
			Return m_conn
		End Get
	End Property

	Public ReadOnly Property DataReader()
		Get
			Return m_dr
		End Get
	End Property

	Public Function openConnection() As SqlConnection

		Try

			If m_conn Is Nothing Then
				m_conn = New SqlConnection(Globals.DBConnection)
			End If

			If m_conn.State = ConnectionState.Closed Then
				m_conn.Open()
			End If
		Catch ex As Exception
            Log.LogData(ex.ToString & "\n" & ex.StackTrace)
        End Try

        Return m_conn
	End Function

	Private Function Command(ByVal sql As String, ByVal conn As SqlConnection) As SqlCommand

		If m_comm Is Nothing Then
			m_comm = New SqlCommand
			m_comm.Connection = conn
		End If

		m_comm.CommandText = sql

		Return m_comm
	End Function

	Public Function query(ByVal sql As String) As SqlDataReader
		Try

			Me.closeReader()
			Me.openConnection()

			Dim dr As SqlDataReader
			Dim conn As SqlConnection = Connection
			Dim comm As SqlCommand = Command(sql, conn)

			m_dr = comm.ExecuteReader()

			Me.movenext()

			Return m_dr
		Catch ex As Exception
            Log.LogData(ex.ToString & "\n" & ex.StackTrace)
		End Try

	End Function

	Public Function nonquery(ByVal sql As String) As Integer
		Try

			Me.closeReader()
			Me.openConnection()
			Dim conn As SqlConnection = Connection
			Dim comm As SqlCommand = Command(sql, conn)

			Return comm.ExecuteNonQuery()
		Catch ex As Exception
            Log.LogData(ex.ToString & "\n" & ex.StackTrace)
		End Try

	End Function

	Public Function SetupStoredProc(ByVal spname As String) As Boolean
		Me.closeReader()
		Dim conn As SqlConnection = Connection
		If m_sp Is Nothing Then
			m_sp = New SqlCommand
		End If
		m_sp.Connection = conn
		m_sp.CommandText = spname	  '= New SqlCommand(spname, conn)
		m_sp.CommandType = CommandType.StoredProcedure

		m_sp.Parameters.Clear()

		Return True
	End Function

	Public Sub AddStoredProcParam(ByVal parmname As String, ByVal parmtype As SqlDbType, ByVal parmval As String)
		Dim param As SqlParameter = m_sp.Parameters.Add(parmname, parmtype)
		param.Value = parmval
	End Sub

	Public Function RunStoredProc() As SqlDataReader
		Try
			Me.openConnection()

			m_dr = m_sp.ExecuteReader()

			Me.movenext()

		Catch ex As Exception
			Log.LogData(ex.ToString & "\n" & ex.StackTrace)
		End Try

		Return m_dr

	End Function

	Public Function movenext() As Boolean
		Dim neof As Boolean = False
		If m_dr Is Nothing Then Return False

		If m_dr.HasRows() Then
			neof = m_dr.Read()
		Else
			Me.closeReader()
		End If

		If neof = False Then
			Me.closeReader()
		End If

		Return neof
	End Function

	Public Function eof() As Boolean
		If m_dr Is Nothing Then Return True

		If m_dr.HasRows = False Then Return True

		Return False
	End Function

	Public Function getval(ByVal col As String, Optional ByVal defaultval As Object = "") As Object
		If m_dr Is Nothing Then
			Return defaultval
		End If

		Try
			If m_dr(col) Is System.DBNull.Value Then
				Return defaultval
			End If

		Catch ex As Exception
			'MsgBox(ex.ToString)
			Return defaultval
		End Try

		Return m_dr(col)

	End Function

	Public Sub close()
		Dim conn As SqlConnection = Connection

		If conn.State = ConnectionState.Open Then
			conn.Close()
		End If

		conn = Nothing
	End Sub

	Public Sub closeReader()
		If m_dr Is Nothing Then
			Exit Sub
		End If

		If m_dr.IsClosed = False Then
			m_dr.Close()
		End If

		m_dr = Nothing
	End Sub

	Public Function quote(ByVal str As String) As String
		str = str.Replace("'", "''")

		Return "'" & str & "'"
	End Function

	Public Function newrow(ByVal table As String, ByVal PrimaryKey As String) As Long
		Dim sql As String
		Dim newval As Long

		sql = "select max(" & PrimaryKey & ") as maxcol from " & table

		Me.query(sql)

		If Me.getval("maxcol").ToString = "" Then
			newval = 0
		Else
			newval = Me.getval("maxcol") + 1
		End If

		sql = "insert into " & table & " (" & PrimaryKey & ") values (" & newval & ")"

		Me.nonquery(sql)

		Return newval
	End Function

	Public Function NewAdapter(ByVal sql As String) As SqlDataAdapter

		Try
			Return New SqlDataAdapter(sql, Globals.DBConnection)
		Catch ex As Exception
			Return Nothing
		End Try

	End Function

	Public Sub BindData(ByVal sql As String, ByVal tblName As String, ByVal dataset As DataSet, ByVal dg As DataGrid)
		Dim data_adapter As SqlDataAdapter

		' Create the SqlDataAdapter.
		data_adapter = New SqlDataAdapter(sql, Globals.DBConnection)

		' Map Table to Contacts.
		data_adapter.TableMappings.Add("Table", tblName)

		' Fill the DataSet.
		dataset = New DataSet
		data_adapter.Fill(dataset)

		' Bind the DataGrid control to the Contacts DataTable.
		dg.SetDataBinding(dataset, tblName)

	End Sub

	Public Function TrimTrailingZeros(ByVal col As String) As String
		Dim inp As Object = getval(col)
		Dim outp As String

		If IsNumeric(inp) Then
			Dim dbl As Double = inp
			outp = dbl.ToString
		Else
			outp = inp
		End If

		Return outp
	End Function
End Class
